import { pool } from "@/db";
import reswrapper from "../reswrapper";
import { RES_MSG } from "@/interfaces/resmsg";
import { formatDateTime } from "@/utils";
//// 统计相关
// 获取报销相关统计数据
export async function getExpenseStatistics(ctx, next) {
  const sql = `SELECT
  COUNT(ID) AS entry_num,
  EXTRACT(DAY FROM (CURRENT_DATE - "max" ("createTime"))) AS min_diff_day,
  COUNT(paydate) AS pay_num,
  MAX(EXTRACT(DAY FROM (paydate - "createTime"))) AS paywait 
FROM
  travel 
WHERE
  date_trunc('year', CURRENT_DATE) <= "createTime";`;
  const sql2 = `SELECT payment::numeric, zzc/payment as percent from travel ORDER BY "createTime" desc LIMIT 1;`;
  const res = await pool.query(sql);
  let obj = res.rows[0] || {};
  const res2 = await pool.query(sql2);
  Object.assign(obj, res2.rows[0] || {});
  ctx.body = reswrapper(obj);
}

// 获取每天工作状态
export async function getDayStatus(ctx, next) {
  const sql = `SELECT
  to_char(d,'yyyy-MM-DD') as date,
  region,
  d <= CURRENT_DATE AS past 
FROM
  generate_series (date_trunc('year', CURRENT_DATE), date_trunc('year', CURRENT_DATE) + INTERVAL '1 year', '1 day') AS D
  LEFT JOIN travel ON travel."startDate" <= D 
  AND D <= travel."endDate" 
WHERE
  to_char(d, 'yyyy') = to_char(CURRENT_DATE, 'yyyy');`;
  const res = await pool.query(sql);
  let list: any[] = res.rows.map((v) => {
    return {
      ...v,
      hasTravel: v.region ? true : false,
    };
  });

  ctx.body = reswrapper(list);
}

// 按城市统计天数
export async function getCityTravelDays(ctx, next) {
  const sql = `SELECT region, sum(days)::int as total from travel GROUP BY region;`;
  const res = await pool.query(sql);
  let list: any[] = res.rows.map((v) => {
    return {
      ...v,
      city: v.region.split(",")[1].replace(/市/g, ""),
    };
  });

  ctx.body = reswrapper(list);
}

/**
 * 今年出差树统计 1.1-12.31
 * @param ctx
 * @param next
 */
export async function getDaysNumOfYear(ctx, next) {
  const sql = `SELECT
  EXTRACT(doy FROM now()) AS daynum,
  SUM(
  LEAST ("endDate", (date_trunc('year', CURRENT_DATE) + INTERVAL '1 year' - INTERVAL '1 day') :: DATE) - GREATEST ("startDate", date_trunc('year', CURRENT_DATE) :: DATE) + 1) AS tarvedays,
  date_part('day', date_trunc('year', CURRENT_DATE) + INTERVAL '1 year' - date_trunc('year', CURRENT_DATE)) AS yeardays 
FROM
  travel 
WHERE
  date_part('year', "endDate") = date_part('year', CURRENT_DATE);`;
  const res = await pool.query(sql);
  let obj: any = {};
  if (res.rows.length > 0) {
    const [{ daynum, tarvedays, yeardays }, ..._rest] = res.rows;
    obj = {
      daynum: +daynum,
      tarvedays: +tarvedays,
      yeardays: +yeardays,
    };
  }
  ctx.body = reswrapper(obj);
}

/**
 * 花费占比
 * @param ctx
 * @param next
 */
export async function getDetailsOfYear(ctx, next) {
  const sql = `SELECT
  SUM(jtf) AS "交通费",
  SUM(zsf) AS "住宿费",
  (SUM(payment) - SUM(zzc)) AS "收入" 
FROM
  travel 
WHERE
  "createTime" >= (now() - INTERVAL '1 year') 
  AND "createTime" < now();`;
  const res = await pool.query(sql);
  let list: any[] = [];
  if (res.rows.length > 0) {
    const [data, ..._rest] = res.rows;
    for (let name in data) {
      list.push({ name, value: Number(data[name].replace(/\$|\,/g, "")) });
    }
  }
  ctx.body = reswrapper(list);
}

/**
 *查询每月出差天数 用出差开始时间来计算
 * @param ctx
 * @param next
 */
export async function getDayCount(ctx, next) {
  const sql = `SELECT
  to_char(T, 'yyyy-MM') "day",
  COALESCE ("sum" (days), 0) AS total 
FROM
  generate_series (date_trunc('month', now()) - INTERVAL '11 month', now(), '1 month')
  AS T LEFT JOIN travel ON to_char(T, 'yyyy-MM') = to_char(travel."startDate", 'yyyy-MM') 
GROUP BY
DAY 
ORDER BY
  "day" ASC;`;
  const res = await pool.query(sql);
  const list = res.rows.map((v) => {
    return {
      ...v,
      total: Number(v.total),
    };
  });
  ctx.body = reswrapper(list);
}

/**
 * 获取最近一年每月的报销数据
 * @param ctx
 * @param next
 */
export async function getLastYearData(ctx, next) {
  const sql = `SELECT
  to_char(T, 'yyyy-MM') "day",
  COALESCE (SUM(payment), '$0') total 
FROM
  generate_series (date_trunc('month', now()) - INTERVAL '11 month', now(), '1 month')
  AS T LEFT JOIN travel ON to_char(T, 'yyyy-MM') = to_char(travel."createTime", 'yyyy-MM') 
GROUP BY
  "day" 
ORDER BY
  "day" ASC;
  `;
  const res = await pool.query(sql);
  const list = res.rows.map((v) => {
    return {
      ...v,
      total: Number(v.total.replace(/\$|\,/g, "")),
    };
  });
  ctx.body = reswrapper(list);
}
